﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Data;

namespace Twi.DataAccess.DBUtility
{
    public class SqlSelect
    {
        /// <summary>
        /// 主表
        /// </summary>
        public string PrimaryTableName = "";

        /// <summary>
        /// 主表别名
        /// </summary>
        public string PrimaryNickName = "";

        /// <summary>
        /// 主表别名
        /// </summary>
        public string PrimaryFirstWhere = "";
        
        /// <summary>
        /// 查询列
        /// </summary>
        public string SelectedColumns = "";
        
        /// <summary>
        /// 查询条件
        /// </summary>
        public string Where = "";


        /// <summary>
        /// Join表
        /// </summary>
        public Dictionary<string, string> DicJoinExpression = new Dictionary<string, string>();

        /// <summary>
        /// 排序
        /// </summary>
        public string OrderExpression = "";

        /// <summary>
        /// 分页属性值
        /// </summary>
        public int PageIndex = 0, PageSize = 0;

        /// <summary>
        /// 继续添加查询列
        /// </summary>
        /// <param name="cols"></param>
        public void AddSelectedColumns(string cols) 
        {
           SelectedColumns += cols;           
        }

        /// <summary>
        /// 设置主表信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="nickName"></param>
        /// <param name="where"></param>
        public void SetPrimaryTable(string tableName, string nickName, string primaryFirstWhere) 
        {
            PrimaryTableName = tableName;
            PrimaryNickName = nickName;
            PrimaryFirstWhere = primaryFirstWhere;
        }

        /// <summary>
        /// 添加Join表
        /// </summary>
        /// <param name="nickName"></param>
        /// <param name="joinExpression"></param>
        public void AddJoinExpression(string nickName, string joinExpression)
        {
            DicJoinExpression.Add(nickName, joinExpression);
        }

        /// <summary>
        /// 添加Where条件
        /// </summary>
        /// <param name="where"></param>
        public void AddWhere(string where) 
        {
            Where += where;
        }

        #region 设置分页信息
        /// <summary>
        /// 设置分页信息
        /// </summary>
        /// <param name="xePage"></param>
        public void SetPageInfo(XElement xePage) 
        {
            if (xePage != null)
            {
                PageSize= int.Parse(xePage.Element("PageSize").Value);
                PageIndex= int.Parse(xePage.Element("PageIndex").Value);
            }
        }
        #endregion 

        #region 获得统计数据SQL
        /// <summary>
        /// 获得统计数据SQL
        /// </summary>
        /// <returns></returns>
        public string GetCountSqlStatement()
        {
            string primaryKeys = SqlExtension.GetMetaDataTablePrimaryKeys(PrimaryTableName)[0];
            StringBuilder sb = new StringBuilder(string.Format("SELECT COUNT({0}) FROM ", PrimaryNickName + "." + primaryKeys));
            sb.Append(PrimaryTableName);
            sb.Append(" ");
            sb.AppendLine(PrimaryNickName);
            foreach (var x in DicJoinExpression) 
            {
                if (Where.Contains(" "+ x.Key + ".")) 
                {
                    sb.AppendLine(x.Value);
                }
            }
            sb.Append("WHERE  ");
            sb.Append(PrimaryFirstWhere);
            sb.Append(Where);
            return sb.ToString();
        }
        #endregion 

        #region 获得分页查询SQL
        /// <summary>
        /// 获得分页查询SQL
        /// </summary>
        /// <returns></returns>
        public string GetPageSqlStatement() 
        {
            StringBuilder sb = new StringBuilder("WITH VIEW_SOURCE AS ( SELECT TOP ");
            if (PageIndex == -1)
            {
                sb.Append("100 PERCENT");
            }
            else
            {
                sb.Append(PageSize * (PageIndex + 1));
            }
            sb.AppendLine(SelectedColumns);
            sb.AppendLine(string.Format(", ROW_NUMBER() OVER (ORDER BY {0}) AS [RowNumber]", OrderExpression ?? "SELECT NULL"));
            sb.AppendLine("FROM");
            sb.Append(PrimaryTableName);
            sb.Append(" ");
            sb.AppendLine(PrimaryNickName);
            foreach (var x in DicJoinExpression)
            {
                if (Where.Contains(" " + x.Key + ".") || SelectedColumns.Contains(" " + x.Key + "."))
                {
                    sb.AppendLine(x.Value);
                }
            }
            sb.AppendLine(string.Format(" WHERE  {0} {1}", PrimaryFirstWhere, Where));
            sb.AppendLine(")");
            if (PageIndex == -1)
            {
                sb.AppendLine(string.Format("SELECT * FROM VIEW_SOURCE ORDER BY [RowNumber]"));
            }
            else
            {
                sb.AppendLine(string.Format("SELECT * FROM VIEW_SOURCE WHERE [RowNumber] > {0} ORDER BY [RowNumber]", PageIndex * PageSize));
            }
            return sb.ToString();
        }
        #endregion

        #region 获得查询语句
        /// <summary>
        /// 获得查询语句
        /// </summary>
        /// <returns></returns>
        public string GetSqlStatement()
        {
            StringBuilder sb = new StringBuilder("SELECT ");
            sb.AppendLine(SelectedColumns);
            sb.AppendLine("FROM");
            sb.Append(PrimaryTableName);
            sb.Append(" ");
            sb.AppendLine(PrimaryNickName);
            foreach (var x in DicJoinExpression)
            {
                if (Where.Contains(" " + x.Key + ".") || SelectedColumns.Contains(" " + x.Key + "."))
                {
                    sb.AppendLine(x.Value);
                }
            }
            sb.AppendLine(string.Format(" WHERE  {0} {1}", PrimaryFirstWhere, Where));
            if (!string.IsNullOrEmpty(OrderExpression))
            {
                sb.Append(string.Format(" ORDER BY {0}", OrderExpression));
            }
            return sb.ToString();
        }
        #endregion

        #region 根据自定义设置查询信息（主表名/主表别名/查询列/查询Join表/查询条件/排序）
        /// <summary>
        /// 根据自定义设置查询信息（主表/主表别名/查询列/查询Join表/查询条件/排序）
        /// </summary>
        /// <param name="dr"></param>
        public void SetSelectInfo(DataRow drSQL, DataRow [] drSQLJoin)
        {
            try
            {
                //如果自定查询主信息为空，怎不用执行如下代码
                if (drSQL == null)
                    return;
                if (!string.IsNullOrEmpty(drSQL["SQLSelect"].ToString()))
                {
                    SelectedColumns = drSQL["SQLSelect"].ToString();
                }

                if (!string.IsNullOrEmpty(drSQL["PrimaryTableName"].ToString()))
                {
                    this.PrimaryTableName = drSQL["PrimaryTableName"].ToString();
                    this.PrimaryNickName = drSQL["PrimaryNickName"].ToString();
                    this.PrimaryFirstWhere = drSQL["PrimaryFirstWhere"].ToString();
                }

                if (!string.IsNullOrEmpty(drSQL["SQLWhere"].ToString()))
                {
                    Where += drSQL["SQLWhere"].ToString();
                }

                if (string.IsNullOrEmpty(OrderExpression) && !string.IsNullOrEmpty(drSQL["SQLOrderBy"].ToString()))
                {
                    OrderExpression = drSQL["SQLOrderBy"].ToString();
                }
                
                //Join表为空，或者记录=0则以下代码不用处理
                if (drSQLJoin == null || drSQLJoin.Count() == 0)
                    return;

                //Join表处理
                DicJoinExpression.Clear();
                foreach (DataRow drJoin in drSQLJoin)
                {
                    DicJoinExpression.Add(drJoin["JoinNickName"].ToString(), drJoin["JoinExpression"].ToString());
                }
            }
            catch { 
            
            }
        }
        #endregion 

    }
}
